Using NONEMPTY Function

Optimization Switches Based on NONEMPTY

Several of the query optimization switches make use of the NONEMPTY function in Microsoft MDX to reduce the number of blanks in the query results. By removing “sparsity” from the query, performance can be dramatically improved in many cases. The improvement is most notable when two or more hierarchies from DIFFERENT dimensions are used on a single query axis (either columns or rows).

The following BI Office optimization switches are based on the NONEMPTY function.

• Optimize Columns
• Optimize Rows
• Measure Optimization
• Optimize Totals
• Filtering Data (see Optimize checkbox in Data Filter dialog)
• N-of-N Iterations (see Optimize checkbox in N-of-N dialog)

Limitations of NONEMPTY

When using the NONEMPTY function, the user should be aware of the following limitations.

No Results for NONEMPTY

The NONEMPTY function can sometimes return no results at all, depending on the cube structure and/or calculation logic. This scenario can typically occur in the following cases.

• There are one or more instances of "ALL levels being empty".
• MDX scope statements that programmatically return no results for the query selections.
• The fact table and dimension table intersect with no results for the selected dimensions and the default model/cube measures.

NONEMPTY Function Causes Slowdown

In some cases, the NONEMPTY function can cause queries to run slower. This can occur when the determination of sparsity takes longer than running the query itself.

Axis with Single Hierarchy

Using the NONEMPTY function for a column or row axis that has a single hierarchy can often prove counterproductive. In these cases, the NONEMPTY function should not be employed.

NONEMPTY vs. NON EMPTY

It is important to remember the differences between the NONEMPTY and NON EMPTY functions in MDX. The practical difference between these two functions is usually minimal, except for cases when:

• Cube is very large.
• Query result set is very large.
• Complex calculations are involved.

NONEMPTY

The NONEMPTY function performs optimization by attempting to eliminate sparsity in the query as part of the query’s functional design.

NON EMPTY

The "NON EMPTY" clause, applied to each axis, attempts to eliminate blank columns or rows from the final data set AFTER the result has been generated.